package cn.gson.crm.model.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import com.mysql.jdbc.StringUtils;

import cn.gson.crm.model.entity.Employee;

public class EmploDao {

	private int pageSize = 5;

	/**
	 * 通过用户的名称获取员工的信息
	 * 
	 * @param userName
	 * @return
	 * @throws SQLException
	 */
	public Employee emploGetByUserName(String status, String userName) throws SQLException {
		String sql = "select * from emplo where status= ? and user_name = ?";
		Map<String, Object> data = Jdbc.getInstace().executeQuery(sql, status, userName.trim());
		Employee emplo = null;
		if (data != null) {
			emplo = buildEmplo(data);

		}
		return emplo;
	}

	/**
	 * 模糊查询员工名字联手机号码
	 * 
	 * @param empName
	 * @return
	 * @throws SQLException
	 */
	public List<String> getNameList(String empName) throws SQLException {
		String sql = "select concat(user_name,telphone)as name  from emplo where user_name like '%"+empName.trim()+"%'";
		System.out.println(sql);
		List<Map<String, Object>> rows = Jdbc.getInstace().executeQueryForList(sql);
		List<String> nameList = null;
		if (rows != null) {
			nameList = new ArrayList<>();
			for (Map<String, Object> row : rows) {
				nameList.add(row.get("name").toString());
			}
		}
		return nameList;
	}

	/**
	 * 通过用户的名称获取员工的信息
	 * 
	 * @param userName
	 * @return
	 * @throws SQLException
	 */
	public Employee emploGetByUserName(String userName) throws SQLException {
		String sql = "select * from emplo where user_name = ?";
		Map<String, Object> data = Jdbc.getInstace().executeQuery(sql, userName.trim());
		Employee emplo = null;
		if (data != null) {
			emplo = buildEmplo(data);

		}
		return emplo;
	}

	/**
	 * 根据学员的部门ID获得所属部门的员工的
	 * 
	 * @param status
	 * @param detpId
	 * @return
	 * @throws SQLException
	 */
	public List<Employee> allEmploGetDeptId(String status, int detpId) throws SQLException {
		String sql = "select * from emplo where status = ? and dept_id = ? ";
		List<Map<String, Object>> data = Jdbc.getInstace().executeQueryForList(sql, status, detpId);

		List<Employee> employee = null;
		if (data != null) {
			employee = new ArrayList<>();
			for (Map<String, Object> map : data) {
				employee.add(buildEmplo(map));
			}
		}
		return employee;
	}

	/**
	 * 根据对应部门ID的所有学员
	 * 
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public List<Map<String, Object>> emploGetDeptId(int id) throws SQLException {
		String sql = "SELECT * FROM `emplo` WHERE emplo.dept_id = ?";
		List<Map<String, Object>> data = Jdbc.getInstace().executeQueryForList(sql, id);
		return data;
	}

	/**
	 * 通过部门ID获取员工信息
	 * 
	 * @param detpId
	 * @return
	 * @throws SQLException
	 */
	public Employee emploGetByDeptId(String status, int detpId) throws SQLException {
		String sql = "select * from emplo where status = ? and dept_id = ?";
		Map<String, Object> data = Jdbc.getInstace().executeQuery(sql, detpId);
		Employee emplo = null;
		if (data != null) {
			emplo = buildEmplo(data);
		}
		return emplo;
	}

	/**
	 * 通过员工id获得员工信息
	 * 
	 * @param status
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public Employee emploGetById(String status, int id) throws SQLException {
		String sql = "select * from emplo where status = ? and id = ?";
		Map<String, Object> data = Jdbc.getInstace().executeQuery(sql, status, id);
		Employee emplo = null;
		if (data != null) {
			emplo = buildEmplo(data);
		}
		return emplo;
	}

	/**
	 * 通过id获取员工信息
	 * 
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public Employee emploGetById(int id) throws SQLException {
		String sql = "select * from emplo where id = ?";
		Map<String, Object> data = Jdbc.getInstace().executeQuery(sql, id);
		Employee emplo = null;
		if (data != null) {
			emplo = buildEmplo(data);
		}
		return emplo;
	}

	/**
	 * 只显示状态为正常的所有用户
	 * 
	 * @param status
	 * @throws SQLException
	 */
	public List<Employee> showStatusEmplo(String status) throws SQLException {
		String sql = "select * from emplo where status = ?";
		List<Map<String, Object>> listData = Jdbc.getInstace().executeQueryForList(sql, status);
		List<Employee> emploList = null;
		if (listData != null && listData.size() > 0) {
			emploList = new ArrayList<>();
			for (Map<String, Object> data : listData) {
				emploList.add(buildEmplo(data));
			}
		}
		return emploList;
	}

	/**
	 * 根据id删除状态为正常的用户并改变状态为删除
	 * 
	 * @param status
	 * @param id
	 * @throws SQLException
	 */
	public boolean emploDeleteById(String status, int id) throws SQLException {
		String sql = "update emplo set status = ? where id = ?";
		return Jdbc.getInstace().executeUpdate(sql, status, id);

	}

	/**
	 * 根据id修改员工的信息
	 * 
	 * @param emplo
	 * @return
	 * @throws SQLException
	 *//*
		 * public boolean changeEmplo(Employee emplo) throws SQLException {
		 * String sql =
		 * "update emplo set user_name = ?,password = ?, gender = ?, telphone = ?, email="
		 * + "?, address = ?, dept_id = ?, emplo_type = ? " +
		 * "where id = ? and status = ? "; System.out.println(sql); return
		 * Jdbc.getInstace().executeUpdate(sql, emplo.getUserName(),
		 * emplo.getPassword(), emplo.getGender(), emplo.getTelphone(),
		 * emplo.getEmail(), emplo.getAddress(), emplo.getDeptId(),
		 * emplo.getEmploType(), emplo.getId(), "正常"); }
		 */

	/**
	 * 获取除了自身之外的其他员工
	 * 
	 * @throws SQLException
	 */
	public List<Employee> getEmploNotContainSelf(String userName) throws SQLException {
		String sql = "select * from emplo where user_name !=? ";
		List<Map<String, Object>> data = Jdbc.getInstace().executeQueryForList(sql, userName);
		List<Employee> empList = new ArrayList<>();
		for (Map<String, Object> empMap : data) {
			empList.add(buildEmplo(empMap));
		}
		return empList;
	}

	/**
	 * 添加修改员工
	 * 
	 * @param emplo
	 * @return
	 * @throws SQLException
	 */
	public boolean addEmplo(Employee emplo) throws SQLException {
		if (emplo.getId() != null) {
			// 修改
			String sql = "update emplo set user_name = ?,password = ?, gender = ?, telphone = ?, email="
					+ "?, address = ?, dept_id = ?, emplo_type = ? " + "where id = ? and status = ? ";
			return Jdbc.getInstace().executeUpdate(sql, emplo.getUserName().trim(), emplo.getPassword().trim(),
					emplo.getGender(), emplo.getTelphone().trim(), emplo.getEmail().trim(), emplo.getAddress().trim(),
					emplo.getDeptId(), emplo.getEmploType(), emplo.getId(), "正常");
		} else {
			// 新建
			String sql = "insert into emplo(user_name,password,gender,telphone,email,address,dept_id,emplo_type)"
					+ " values(?,?,?,?,?,?,?,?)";
			return Jdbc.getInstace().executeUpdate(sql, emplo.getUserName(), emplo.getPassword(), emplo.getGender(),
					emplo.getTelphone().trim(), emplo.getEmail().trim(), emplo.getAddress().trim(), emplo.getDeptId(),
					emplo.getEmploType());
		}
	}

	/**
	 * 获取总页数
	 * 
	 * @param status
	 * @return
	 * @throws SQLException
	 */
	public int getTotalPage(String status) throws SQLException {
		String sql = "select count(*) as total from emplo where status = ?";
		Map<String, Object> data = Jdbc.getInstace().executeQuery(sql, status);
		Integer total = (Integer) data.get("total");
		return (int) Math.ceil(total.doubleValue() / pageSize);

	}

	/**
	 * 搜素总页数
	 * 
	 * @param status
	 * @return
	 * @throws SQLException
	 */
	public int getTotalPage(String status, String userName, String dept) throws SQLException {
		String sql = "select count(*) as total from emplo where status = ? ";
		List<Object> params = new ArrayList<>();
		sql = buildSqlAndParams(params, sql, status, userName, dept);
		Map<String, Object> data = Jdbc.getInstace().executeQuery(sql, params.toArray());
		Integer total = Integer.parseInt(data.get("total").toString());
		return (int) Math.ceil(total.doubleValue() / pageSize);

	}

	/**
	 * 知道姓名查询的搜索总页数
	 * 
	 * @param status
	 * @param userName
	 * @return
	 * @throws SQLException
	 */
	public int getTotalPage(String status, String userName) throws SQLException {
		String sql = "select count(*) as total from emplo where status = ? ";
		List<Object> params = new ArrayList<>();
		sql = buildSqlAndParams(params, sql, status, userName);
		Map<String, Object> data = Jdbc.getInstace().executeQuery(sql, params.toArray());
		Integer total = Integer.parseInt(data.get("total").toString());
		return (int) Math.ceil(total.doubleValue() / pageSize);

	}

	/**
	 * 查询分页数据
	 * 
	 * @param status
	 * @param page
	 * @return
	 * @throws SQLException
	 */
	public List<Employee> getPageEmploByStatus(String status, int page) throws SQLException {
		int start = (page - 1) * pageSize;
		String sql = "select * from emplo where status = ? order by id desc limit ?,?";
		return queryEmploList(sql, status, start, pageSize);
	}

	/**
	 * 带条件的分页查询
	 * 
	 * @param status
	 * @param page
	 * @param userName
	 * @param dept
	 * @return
	 * @throws SQLException
	 */
	public List<Employee> getPageEmploByStatus(String status, int page, String userName, String dept)
			throws SQLException {
		int start = (page - 1) * pageSize;
		String sql = "select * from emplo where status = ?";
		List<Object> params = new ArrayList<>();
		sql = buildSqlAndParams(params, sql, status, userName, dept);
		sql += "order by id desc limit ?,?";
		params.add(start);
		params.add(pageSize);
		return queryEmploList(sql, params.toArray());

	}

	/**
	 * 只带姓名条件的分页查询
	 * 
	 * @param status
	 * @param page
	 * @param userName
	 * @return
	 * @throws SQLException
	 */
	public List<Employee> getPageEmploByStatus(String status, int page, String userName) throws SQLException {
		int start = (page - 1) * pageSize;
		String sql = "select * from emplo where status = ?";
		List<Object> params = new ArrayList<>();
		sql = buildSqlAndParams(params, sql, status, userName);
		sql += "order by id desc limit ?,?";
		params.add(start);
		params.add(pageSize);
		return queryEmploList(sql, params.toArray());

	}

	private List<Employee> queryEmploList(String sql, Object... params) throws SQLException {
		List<Map<String, Object>> listData = Jdbc.getInstace().executeQueryForList(sql, params);
		List<Employee> emplo = null;
		if (listData != null && listData.size() > 0) {
			emplo = new ArrayList<>();
			for (Map<String, Object> data : listData) {
				emplo.add(buildEmplo(data));
			}
		}
		return emplo;
	}

	private String buildSqlAndParams(List<Object> params, String sql, String status, String userName, String dept) {
		params.add(status);
		if (!StringUtils.isNullOrEmpty(userName)) {
			sql += "and user_name like ?";
			params.add("%" + userName.trim() + "%");
		}
		if (!StringUtils.isNullOrEmpty(dept)) {
			sql += "and dept_id like ?";
			params.add("%" + dept.trim() + "%");
		}
		return sql;
	}

	private String buildSqlAndParams(List<Object> params, String sql, String status, String userName) {
		params.add(status);
		if (!StringUtils.isNullOrEmpty(userName)) {
			sql += "and user_name like ?";
			params.add("%" + userName.trim() + "%");
		}
		return sql;
	}

	private Employee buildEmplo(Map<String, Object> data) {
		Employee emplo = new Employee();
		emplo.setId(Integer.parseInt(data.get("id").toString()));
		emplo.setUserName((String) data.get("user_name"));
		emplo.setPassword((String) data.get("password"));
		emplo.setGender((String) data.get("gender"));
		emplo.setTelphone((String) data.get("telphone"));
		emplo.setEmail((String) data.get("email"));
		emplo.setAddress((String) data.get("address"));
		emplo.setAvatar((String) data.get("avatar"));
		if (data.get("dept_id") != null) {
			emplo.setDeptId((Integer.parseInt(data.get("dept_id").toString())));
		}
		emplo.setEmploType((Integer.parseInt(data.get("emplo_type").toString())));
		if (data.get("status") != null) {
			emplo.setStatus((String) data.get("status"));
		}

		emplo.setRegTime((Date) data.get("create_date"));
		return emplo;
	}

}
